# -*- coding: utf-8 -*-

#Joao Alturas_6197
#2014_10_16

import csv
import sys
import numpy as np
import xlrd
import matplotlib.pyplot as plt

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy import Column, Integer, String, func

from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///treta.db')

Base = declarative_base()

"""
#Create base dados 

"""
class Dados(Base):
	
	__tablename__ = 'dados'	
	
	id = Column(Integer, primary_key=True)						
	ano = Column(String(6))
	ipcGlobal = Column(String(10)) 	 
	varAnualTaxInfl = Column(String(10))     
	remMinMensal = Column(String(10))   
	remMaxMensal = Column(String(10)) 
	pibPercapitAnual = Column(String(10)) 
	rendNacBrutPercAnual = Column (String(10))  
	rendDispBrutPercAnual = Column (String(10))  
	remunPercapitAnual = Column (String(10)) 
	
    
	def __init__(self, ano,ipcGlobal,varAnualTaxInfl,remMinMensal,remMaxMensal,
		pibPercapitAnual,rendNacBrutPercAnual,rendDispBrutPercAnual,
                     remunPercapitAnual):
											 
			self.ano = ano
			self.ipcGlobal = ipcGlobal	 
			self.varAnual = varAnualTaxInfl
			self.remMinMensal = remMinMensal
			self.remMaxMensal= remMaxMensal
			self.pibPercapitAnual = pibPercapitAnual
			self.rendNacBrutPercAnual = rendNacBrutPercAnual
			self.rendDispBrutPercAnual = rendDispBrutPercAnual
			self.remunPercapitAnual = remunPercapitAnual
            
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()


def xlread(arq_xls):
    """
    Gerador que le arquivo .xls
    """
    # Abre o arquivo
    
    xls = xlrd.open_workbook(arq_xls)
    
    # Le a primeira linha do arquivo
    
    plan = xls.sheets()[0]

    # Para i de zero ao numero de linhas do arquivo
    
    for i in xrange(plan.nrows):
		
        # Le os valores nas linhas do arquivo
        
        yield plan.row_values(i)

for linha in xlread('csvFile.xls'):
    print linha



spamReader = csv.reader(open("csvFile.xls", "r"), delimiter=',')
x = 0
for row in spamReader: # Read row of csvFile
	for i in range(0,8):
		row[i] = row[i].replace(",", r'.');
		row[i] = row[i].replace("\\", r'\\\\');
		row[i] = row[i].decode("utf-8")
		pass
	try:
		dado = Dados(row[0], row[1],row[2], row[3], row[4], row[5], row[6], row[7], row[8])
		session.add(dado)
		session.commit()
	except Exception, err:
		print err
		print row
		x = 1



